1 <?php
2 // This script and data application were generated by AppGini 5.62
3 // Download AppGini for free from https://bigprof.com/appgini/download/
4
5 /*
6 ajax-callable script that returns code for either a combo drop-down or an auto-complete
7 drop-down, based on number of items.
8
9 REQUEST parameters:
10 ===============
11 t: table name
12 f: lookup field name
13 id: selected id
14 p: page number (default = 1)
15 s: search term
16 o: 0 (default) for text-only or 1 for full options list
17 text: selected text
18 filterer_[filterer]: name of filterer field to be used to filter the drop-down contents
19 must be one of the filteres defined for the concerned field
20 */
21
22 $start_ts = microtime(true);
23
24 // how many results to return per call, in case of json output
25 $results_per_page = 50;
26
27 $curr_dir = dirname(__FILE__);
28 include("$curr_dir/defaultLang.php");
29 include("$curr_dir/language.php");
30 include("$curr_dir/lib.php");
31
32 handle_maintenance();
33
34 // drop-downs config
35 $lookups = array(
36 'patients' => array(
37 ),
38 'disease_symptoms' => array(
39 ),
40 'medical_records' => array(
41 'patient' => array(
42 'parent_table' => 'patients',
43 'parent_pk_field' => 'id',
44 'parent_caption' => 'IF(CHAR_LENGTH(`patients`.`last_name`) || CHAR_LENGTH(`patients`.`first_name`), CONCAT_WS(\'\', `patients`.`last_name`, \',\', `patients`.`first_name`), \'\')',
45 'parent_from' => '`patients` ',
46 'filterers' => array(),
47 'custom_query' => '',
48 'inherit_permissions' => false,
49 'list_type' => 0,
50 'not_null' => false
51 )
52 ),
53 'events' => array(
54 'name_patient' => array(
55 'parent_table' => 'patients',
56 'parent_pk_field' => 'id',
57 'parent_caption' => 'IF(CHAR_LENGTH(`patients`.`last_name`) || CHAR_LENGTH(`patients`.`first_name`), CONCAT_WS(\'\', `patients`.`last_name`, \',\', `patients`.`first_name`), \'\')',
58 'parent_from' => '`patients` ',
59 'filterers' => array(),
60 'custom_query' => '',
61 'inherit_permissions' => false,
62 'list_type' => 0,
63 'not_null' => false
64 )
65 )
66 );
67
68 // XSS prevention
69 $xss = new CI_Input();
70 $xss->charset = datalist_db_encoding;
71
72 // receive and verify user input
73 $table_name = $_REQUEST['t'];
74 $field_name = $_REQUEST['f'];
75 $search_id = makeSafe(iconv('UTF-8', datalist_db_encoding, $_REQUEST['id']));
76 $selected_text = iconv('UTF-8', datalist_db_encoding, $_REQUEST['text']);
77 $returnOptions = ($_REQUEST['o'] == 1 ? true : false);
78 $page = intval($_REQUEST['p']);
79 if($page < 1) $page = 1;
80 $skip = $results_per_page * ($page - 1);
81 $search_term = makeSafe(iconv('UTF-8', datalist_db_encoding, $_REQUEST['s']));
82
83 if(!isset($lookups[$table_name][$field_name])) die('{ "error": "Invalid table or field." }');
84
85 // can user access the requested table?
86 $perm = getTablePermissions($table_name);
87 if(!$perm[0] && !$search_id) die('{ "error": "' . addslashes($Translation['tableAccessDenied']) . '" }');
88
89 $field = $lookups[$table_name][$field_name];
90
91 $wheres = array();
92
93 // search term provided?
94 if($search_term){
95 $wheres[] = "{$field['parent_caption']} like '%{$search_term}%'";
96 }
97
98 // any filterers specified?
99 if(is_array($field['filterers'])){
100 foreach($field['filterers'] as $filterer => $filterer_parent){
101 $get = (isset($_REQUEST["filterer_{$filterer}"]) ? $_REQUEST["filterer_{$filterer}"] : false);
102 if($get){
103 $wheres[] = "`{$field['parent_table']}`.`$filterer_parent`='" . makeSafe($get) . "'";
104 }
105 }
106 }
107
108 // inherit permissions?
109 if($field['inherit_permissions']){
110 $inherit = permissions_sql($field['parent_table']);
111 if($inherit === false && !$search_id) die($Translation['tableAccessDenied']);
112
113 if($inherit['where']) $wheres[] = $inherit['where'];
114 if($inherit['from']) $field['parent_from'] .= ", {$inherit['from']}";
115 }
116
117 // single value?
118 if($field['list_type'] != 2 && $search_id){
119 $wheres[] = "`{$field['parent_table']}`.`{$field['parent_pk_field']}`='{$search_id}'";
120 }
121
122 if(count($wheres)){
123 $where = 'WHERE ' . implode(' AND ', $wheres);
124 }
125
126 // define the combo and return the code
127 $combo = new DataCombo;
128 if($field['custom_query']){
129 $qm = array(); $custom_where = ''; $custom_order_by = '2';
130 $combo->Query = $field['custom_query'];
131
132 if(preg_match('/ order by (.*)$/i', $combo->Query, $qm)){
133 $custom_order_by = $qm[1];
134 $combo->Query = preg_replace('/ order by .*$/i', '', $combo->Query);
135 }
136
137 if(preg_match('/ where (.*)$/i', $combo->Query, $qm)){
138 $custom_where = $qm[1];
139 $combo->Query = preg_replace('/ where .*$/i', '', $combo->Query);
140 }
141
142 if($where && $custom_where){
143 $combo->Query .= " {$where} AND ({$custom_where}) ORDER BY {$custom_order_by}";
144 }elseif($custom_where){
145 $combo->Query .= " WHERE {$custom_where} ORDER BY {$custom_order_by}";
146 }else{
147 $combo->Query .= " {$where} ORDER BY {$custom_order_by}";
148 }
149
150 $query_match = array();
151 preg_match('/select (.*) from (.*)$/i', $combo->Query, $query_match);
152
153 if(isset($query_match[2])){
154 $count_query = "SELECT count(1) FROM {$query_match[2]}";
155 }else{
156 $count_query = '';
157 }
158 }else{
159 $combo->Query = "SELECT " . ($field['inherit_permissions'] ? 'DISTINCT ' : '') . "`{$field['parent_table']}`.`{$field['parent_pk_field']}`, {$field['parent_caption']} FROM {$field['parent_from']} {$where} ORDER BY 2";
160 $count_query = "SELECT count(1) FROM {$field['parent_from']} {$where}";
161 }
162 $combo->table = $table_name;
163 $combo->parent_table = $field['parent_table'];
164 $combo->SelectName = $field_name;
165 $combo->ListType = $field['list_type'];
166 if($search_id){
167 $combo->SelectedData = $search_id;
168 }elseif($selected_text){
169 $combo->SelectedData = getValueGivenCaption($combo->Query, $selected_text);
170 }
171
172 if($field['list_type'] == 2){
173 $combo->Render();
174 $combo->HTML = str_replace('<select ', '<select onchange="' . $field_name . '_changed();" ', $combo->HTML);
175
176 // return response
177 if($returnOptions){
178 ?><span id="<?php echo $field_name; ?>-combo-list"><?php echo $combo->HTML; ?></span><?php
179 }else{
180 ?>
181 <span id="<?php echo $field_name; ?>-match-text"><?php echo $combo->MatchText; ?></span>
182 <input type="hidden" id="<?php echo $field_name; ?>" value="<?php echo html_attr($combo->SelectedData); ?>" />
183 <?php
184 }
185 }else{
186 /* return json */
187 header('Content-type: application/json');
188
189 if(!preg_match('/ limit .+/i', $combo->Query)){
190 if(!$search_id) $combo->Query .= " LIMIT {$skip}, {$results_per_page}";
191 if($search_id) $combo->Query .= " LIMIT 1";
192 }
193
194 $prepared_data = array();
195
196 // specific caption provided and list_type is not radio?
197 if(!$search_id && $selected_text){
198 $search_id = getValueGivenCaption($combo->Query, $selected_text);
199 if($search_id) $prepared_data[] = array('id' => $search_id, 'text' => $xss->xss_clean($selected_text));
200 }else{
201 $res = sql($combo->Query, $eo);
202 while($row = db_fetch_row($res)){
203 if(empty($prepared_data) && $page == 1 && !$search_id && !$field['not_null']){
204 $prepared_data[] = array('id' => empty_lookup_value, 'text' => "<{$Translation['none']}>");
205 }
206
207 $prepared_data[] = array('id' => iconv(datalist_db_encoding, 'UTF-8', $row[0]), 'text' => iconv(datalist_db_encoding, 'UTF-8', $xss->xss_clean($row[1])));
208 }
209 }
210
211 if(empty($prepared_data)){ $prepared_data[] = array('id' => '', 'text' => $Translation['No matches found!']); }
212
213 echo json_encode(array(
214 'results' => $prepared_data,
215 'more' => (@db_num_rows($res) >= $results_per_page),
216 'elapsed' => round(microtime(true) - $start_ts, 3)
217 ));
218 }
